package SQLite;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;

import com.example.hcmiu.englishforkid.GameListenAndGuessActivity;

public class DatabaseHandler2 extends SQLiteOpenHelper {

	// All Static variables
	// Database Version
	private static final int DATABASE_VERSION = 1;

	// Database Name
	private static final String DATABASE_NAME = "appManager";

	public DatabaseHandler2(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}

	// Creating Tables
	@Override
	public void onCreate(SQLiteDatabase db) {

		String CREATE_HISTORY_GAME_LISTEN_TABLE = "CREATE TABLE game_listen_and_choose(id TEXT,correct INTEGER,total INTEGER)";
		db.execSQL(CREATE_HISTORY_GAME_LISTEN_TABLE);

		createDefault(db);

	}

	private void createDefault(SQLiteDatabase db) {
		for (String key : GameListenAndGuessActivity.GameItem.types) {

			ContentValues values = new ContentValues();
			values.put("id", key);
			values.put("correct", 0);
			values.put("total", 0);
			// Inserting Row
			db.insert("game_listen_and_choose", null, values);

		}
	}

	// Upgrading database
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {

		db.execSQL("DROP TABLE IF EXISTS game_listen_and_choose");

		// Create tables again
		onCreate(db);
	}

	// Getting All Contacts
	public String getAllResult() {

		StringBuilder sb = new StringBuilder();

		String selectQuery = "SELECT  * FROM game_listen_and_choose";

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		sb.append("<table>\r\n");

		sb.append("<tr>\r\n" + "  <td>TYPE</td>\r\n"
				+ "  <td>Correct</td> \r\n" + "  <td>Total Question</td>\r\n"
				+ "</tr>\r\n");

		// looping through all rows and adding to list
		if (cursor.moveToFirst()) {
			do {
				String v1 = cursor.getString(0);
				String v2 = cursor.getString(1);
				String v3 = cursor.getString(2);

				System.out.println(v1 + "::" + v2 + "-" + v3);

				sb.append("<tr>\r\n" + "  <td>" + v1 + "</td>\r\n" + "  <td>"
						+ v2 + "</td> \r\n" + "  <td>" + v3 + "</td>\r\n"
						+ "</tr>\r\n");

			} while (cursor.moveToNext());
		}

		sb.append("</table>");

		System.out.println(sb.toString());

		// return contact list
		return sb.toString();
	}

	// Updating single contact
	public int updateAnswer(String type, boolean isCorrect) {

		SQLiteDatabase db = this.getWritableDatabase();

		Cursor cursor = db.query("game_listen_and_choose", new String[] {
				"correct", "total" }, "id=?",
				new String[] { String.valueOf(type) }, null, null, null, null);
		if (cursor != null)
			cursor.moveToFirst();

		int correct = cursor.getInt(0);
		int total = cursor.getInt(0) + 1;

		correct = isCorrect ? correct + 1 : correct;

		ContentValues values = new ContentValues();
		values.put("correct", correct);
		values.put("total", total);

		// updating row
		return db.update("game_listen_and_choose", values, "id = ?",
				new String[] { String.valueOf(type) });
	}
	//
	// // Deleting single contact
	// public void deleteContact(Contact contact) {
	// SQLiteDatabase db = this.getWritableDatabase();
	// db.delete(TABLE_CONTACTS, KEY_ID + " = ?",
	// new String[] { String.valueOf(contact.getID()) });
	// db.close();
	// }

	// // Getting contacts Count
	// public int getContactsCount() {
	// String countQuery = "SELECT  * FROM " + TABLE_CONTACTS;
	// SQLiteDatabase db = this.getReadableDatabase();
	// Cursor cursor = db.rawQuery(countQuery, null);
	// cursor.close();
	//
	// // return count
	// return cursor.getCount();
	// }

}
